/*  Script used to create stored procedures for the
	NetSys database and application.
*/

USE NETSYS
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* Gets a list of applicants for the View Applicants table */
CREATE PROCEDURE GetApplicants
AS
BEGIN
	SET NOCOUNT ON;
	SELECT
		FIRST_NAME,
		LAST_NAME,
		PHONE_NUMBER,
		EMAIL,
		INTERVIEW_DATE,
		APPLICANT_KEY,
		POSITION.POSITION_TITLE,
		POSITION.POSITION_ID,
		APPLICANT.APPLICANT_ID
	FROM APPLICANT
	JOIN POSITION ON POSITION.POSITION_ID = APPLICANT.POSITION_ID
END
GO

/* Sets up a quiz attempt for a certain applicant */
CREATE PROCEDURE StartQuiz
	@APPLICANT_ID INT,
	@ATTEMPT_ID INT OUTPUT
AS
BEGIN
	
	DECLARE @ATTEMPT_COUNT INT
	DECLARE @QUIZ_ID INT
	SET @ATTEMPT_ID = -1

	SELECT @ATTEMPT_COUNT=COUNT(*) FROM QUIZ_ATTEMPT WHERE APPLICANT_ID=@APPLICANT_ID
	IF @ATTEMPT_COUNT = 0
	BEGIN
		
		SELECT @QUIZ_ID=QUIZ_ID FROM POSITION
		JOIN APPLICANT ON POSITION.POSITION_ID=APPLICANT.POSITION_ID
		WHERE APPLICANT_ID=@APPLICANT_ID
		
		INSERT INTO QUIZ_ATTEMPT(APPLICANT_ID, ATTEMPT_DATE, QUIZ_ID)
		VALUES(@APPLICANT_ID, GETDATE(), @QUIZ_ID)
		
		SET @ATTEMPT_ID=@@IDENTITY	
	END
	ELSE
		SELECT @ATTEMPT_ID=ATTEMPT_ID FROM QUIZ_ATTEMPT WHERE APPLICANT_ID=@APPLICANT_ID
END
GO

/* Gets the next question that should be answered for a quiz attempt */
CREATE PROCEDURE GetAttemptNextQuestion
	@ATTEMPT_ID INT
AS
BEGIN

	DECLARE @QR_COUNT INT
	DECLARE @QUIZ_ID INT
	DECLARE @NQ_ID INT

	SELECT @QR_COUNT=COUNT(*) FROM QUESTION_RESULT WHERE ATTEMPT_ID=@ATTEMPT_ID

	/* If the result count is zero, start a new quiz attempt */
	IF @QR_COUNT = 0
	BEGIN
		SELECT @QUIZ_ID=QUIZ_ID FROM QUIZ_ATTEMPT WHERE ATTEMPT_ID=@ATTEMPT_ID
		SELECT MIN(QUESTION_ID) AS NEXT_QUESTION_ID FROM QUIZ_QUESTION WHERE QUIZ_ID=@QUIZ_ID
	END
	ELSE
		SELECT @QUIZ_ID=QUIZ_ID FROM QUIZ_ATTEMPT WHERE ATTEMPT_ID=@ATTEMPT_ID
		SELECT @NQ_ID=MIN(QUESTION_ID) FROM QUIZ_QUESTION WHERE QUIZ_ID=@QUIZ_ID
			AND QUESTION_ID NOT IN
			(SELECT QUESTION_RESULT.QUESTION_ID FROM QUESTION_RESULT WHERE ATTEMPT_ID=@ATTEMPT_ID)
		IF @NQ_ID IS NULL
		BEGIN
			SELECT -1 AS NEXT_QUESTION_ID
		END
		ELSE
			SELECT @NQ_ID AS NEXT_QUESTION_ID

END
GO

/* Gets the previous question that was answered for a quiz attempt */
CREATE PROCEDURE GetAttemptPreviousQuestion
	@ATTEMPT_ID INT
AS
BEGIN

	DECLARE @QR_COUNT INT
	DECLARE @QUIZ_ID INT
	DECLARE @PQ_ID INT

	SELECT @QR_COUNT=COUNT(*) FROM QUESTION_RESULT WHERE ATTEMPT_ID=@ATTEMPT_ID

	/* If the result count is zero, there are no previous questions. */
	IF @QR_COUNT = 0
	BEGIN
		SELECT -1 AS PREV_QUESTION_ID
	END
	ELSE
		SET ROWCOUNT 1
		SELECT
			MAX(QUESTION_ID) AS PREV_QUESTION_ID FROM QUESTION_RESULT
		WHERE
			QUESTION_RESULT.ATTEMPT_ID=@ATTEMPT_ID
		GROUP BY QUESTION_ID
		ORDER BY QUESTION_ID DESC

END
GO

/* Gets a list of all of the quizzes that are defined */
CREATE PROCEDURE GetQuizList
AS
BEGIN
	SELECT
		QUIZ.QUIZ_NAME, QUIZ.ALLOW_BACKTRACK, QUIZ.QUIZ_ID,
		COUNT(QUIZ_QUESTION.QUESTION_ID) AS QUESTION_COUNT
	FROM QUIZ_QUESTION
	RIGHT OUTER JOIN QUIZ ON QUIZ.QUIZ_ID=QUIZ_QUESTION.QUIZ_ID
	GROUP BY QUIZ.QUIZ_NAME, QUIZ.ALLOW_BACKTRACK, QUIZ.QUIZ_ID
END
GO

/* Gets a list of positions for the View Positions table */
CREATE PROCEDURE GetPositionList
AS
BEGIN
	SELECT
		POSITION.POSITION_TITLE, POSITION.POSITION_ID,
		POSITION.HIRE_DATE, QUIZ.QUIZ_NAME, QUIZ.QUIZ_ID
	FROM POSITION
	JOIN QUIZ ON POSITION.QUIZ_ID=QUIZ.QUIZ_ID
END
GO

/* Gets a list of all questions for the question bank */
CREATE PROCEDURE GetQuestionList
AS
BEGIN
	SELECT QUESTION.QUESTION_ID, QUESTION.QUESTION_TEXT, QUESTION.QUESTION_TYPE,
	QUESTION_CATEGORY.CATEGORY_NAME, QUESTION_LEVEL.LEVEL_DESCRIPTION
	FROM QUESTION
	JOIN QUESTION_CATEGORY ON QUESTION.CATEGORY_ID=QUESTION_CATEGORY.CATEGORY_ID
	JOIN QUESTION_LEVEL ON QUESTION_LEVEL.LEVEL_ID=QUESTION.LEVEL_ID
END
GO

/* Gets a list of all quiz attempts */
CREATE PROCEDURE GetAttemptList
AS
BEGIN
	SELECT
		APPLICANT.FIRST_NAME + ' ' + APPLICANT.LAST_NAME AS FULL_NAME,
		APPLICANT.APPLICANT_ID,	QUIZ_ATTEMPT.ATTEMPT_DATE,
		QUIZ_ATTEMPT.ATTEMPT_ID, QUIZ_ATTEMPT.GRADED, QUIZ_ATTEMPT.QUIZ_ID,
	[NETSYS].[dbo].GetQuizCorrect(QUIZ_ATTEMPT.ATTEMPT_ID) AS CORRECT,
	[NETSYS].[dbo].GetQuizIncorrect(QUIZ_ATTEMPT.ATTEMPT_ID) AS INCORRECT
	FROM APPLICANT
	JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.APPLICANT_ID=APPLICANT.APPLICANT_ID
END
GO

/* Gets a list of quiz attempts that were completed in the last 30 days */
CREATE PROCEDURE GetRecentAttempts
AS
BEGIN
	SELECT APPLICANT.FIRST_NAME + ' ' + APPLICANT.LAST_NAME AS FULL_NAME, APPLICANT.APPLICANT_ID,
	QUIZ_ATTEMPT.ATTEMPT_DATE, QUIZ_ATTEMPT.ATTEMPT_ID
	FROM APPLICANT
	JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.APPLICANT_ID=APPLICANT.APPLICANT_ID
	WHERE QUIZ_ATTEMPT.ATTEMPT_DATE < DATEADD(day, 30, GETDATE())
END
GO

/* Gets the applicant response detail for a quiz attempt */
CREATE PROCEDURE GetAttemptDetail
	@ATTEMPT_ID int
AS
BEGIN

	SELECT
		QUESTION_RESULT.QUESTION_ID, QUESTION_RESULT.ATTEMPT_ID,
		QUESTION_RESULT.ANSWERED_CORRECTLY,	SHORT_ANSWER.ANSWER_TEXT,
		QUESTION.QUESTION_TEXT, QUESTION.QUESTION_TYPE
	FROM QUESTION_RESULT
	LEFT OUTER JOIN SHORT_ANSWER ON SHORT_ANSWER.QUESTION_ID=QUESTION_RESULT.QUESTION_ID
		AND SHORT_ANSWER.ATTEMPT_ID=QUESTION_RESULT.ATTEMPT_ID
	JOIN QUESTION ON QUESTION.QUESTION_ID=QUESTION_RESULT.QUESTION_ID
	WHERE QUESTION_RESULT.ATTEMPT_ID=@ATTEMPT_ID

END
GO

/* Gets the level statistics for a certain level and certain quiz attempt */
CREATE PROCEDURE GetApplicantLevelStatistics
	@ATTEMPT_ID int,
	@LEVEL_ID int
AS
BEGIN
	DECLARE @CORRECT_COUNT INT
	DECLARE @LEVEL_DESCRIPTION varchar(30)
	SELECT @CORRECT_COUNT=COUNT(QUESTION_RESULT.ANSWERED_CORRECTLY)
	FROM QUESTION_RESULT
		JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.ATTEMPT_ID=QUESTION_RESULT.ATTEMPT_ID
		JOIN QUESTION ON QUESTION_RESULT.QUESTION_ID=QUESTION.QUESTION_ID
		LEFT OUTER JOIN QUESTION_LEVEL ON QUESTION_LEVEL.LEVEL_ID=QUESTION.LEVEL_ID
	WHERE
		QUESTION_RESULT.ANSWERED_CORRECTLY=1 AND
		QUESTION.LEVEL_ID=@LEVEL_ID AND
		QUIZ_ATTEMPT.ATTEMPT_ID=@ATTEMPT_ID
	SELECT @LEVEL_DESCRIPTION=LEVEL_DESCRIPTION FROM QUESTION_LEVEL WHERE LEVEL_ID=@LEVEL_ID
	SELECT @CORRECT_COUNT AS CORRECT_COUNT, @LEVEL_DESCRIPTION AS LEVEL_DESCRIPTION
END
GO

/* Gets the level statistics for a certain level and certain quiz
   (i.e. how many Level 2 questions are in the Networking quiz)
*/
CREATE PROCEDURE GetQuizLevelStatistics
	@ATTEMPT_ID int,
	@LEVEL_ID int
AS
BEGIN
	SELECT COUNT(*) AS QUESTION_COUNT FROM QUIZ_QUESTION
	JOIN QUESTION ON QUESTION.QUESTION_ID=QUIZ_QUESTION.QUESTION_ID
	JOIN QUIZ ON QUIZ.QUIZ_ID=QUIZ_QUESTION.QUIZ_ID
	JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.QUIZ_ID = QUIZ.QUIZ_ID
	WHERE QUESTION.LEVEL_ID=@LEVEL_ID
		AND QUIZ_ATTEMPT.ATTEMPT_ID=@ATTEMPT_ID
END
GO

/* Gets the category statistics for a certain category and certain quiz attempt */
CREATE PROCEDURE GetApplicantCategoryStatistics
	@ATTEMPT_ID int,
	@CATEGORY_ID int
AS
BEGIN
	DECLARE @CORRECT_COUNT INT
	DECLARE @CATEGORY_NAME varchar(40)
	SELECT @CORRECT_COUNT=COUNT(QUESTION_RESULT.ANSWERED_CORRECTLY)
	FROM QUESTION_RESULT
		JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.ATTEMPT_ID=QUESTION_RESULT.ATTEMPT_ID
		JOIN QUESTION ON QUESTION_RESULT.QUESTION_ID=QUESTION.QUESTION_ID
		LEFT OUTER JOIN QUESTION_LEVEL ON QUESTION_LEVEL.LEVEL_ID=QUESTION.LEVEL_ID
	WHERE
		QUESTION_RESULT.ANSWERED_CORRECTLY=1 AND
		QUESTION.CATEGORY_ID=@CATEGORY_ID AND
		QUIZ_ATTEMPT.ATTEMPT_ID=@ATTEMPT_ID
	SELECT @CATEGORY_NAME=CATEGORY_NAME FROM QUESTION_CATEGORY WHERE CATEGORY_ID=@CATEGORY_ID
	SELECT @CORRECT_COUNT AS CORRECT_COUNT, @CATEGORY_NAME AS CATEGORY_NAME
END
GO

/* Gets the category statistics for a certain category and certain quiz
   (i.e. how many Networking questions are in the Networking quiz)
*/
CREATE PROCEDURE GetQuizCategoryStatistics
	@ATTEMPT_ID int,
	@CATEGORY_ID int
AS
BEGIN
	SELECT COUNT(*) AS QUESTION_COUNT FROM QUIZ_QUESTION
	JOIN QUESTION ON QUESTION.QUESTION_ID=QUIZ_QUESTION.QUESTION_ID
	JOIN QUIZ ON QUIZ.QUIZ_ID=QUIZ_QUESTION.QUIZ_ID
	JOIN QUIZ_ATTEMPT ON QUIZ_ATTEMPT.QUIZ_ID = QUIZ.QUIZ_ID
	WHERE QUESTION.CATEGORY_ID=@CATEGORY_ID
		AND QUIZ_ATTEMPT.ATTEMPT_ID=@ATTEMPT_ID
END
GO